delimiter $$

DROP TABLE IF EXISTS `msg_user`$$
DROP TABLE IF EXISTS `msg_messages`$$
DROP PROCEDURE IF EXISTS `msg_mark_message_has_read`$$

delimiter $$

CREATE TABLE `msg_messages` (
  `message_id` char(32) NOT NULL COMMENT 'Id of message',
  `title` varchar(200) NOT NULL DEFAULT '' COMMENT 'Title of message',
  `content` varchar(4000) NOT NULL DEFAULT '' COMMENT 'Content of message',
  `receiver_id` char(32) DEFAULT NULL COMMENT 'Id of receiver, empty id means all users.',
  `sender_id` char(32) DEFAULT NULL COMMENT 'Id of sender',
  `send_date` datetime DEFAULT NULL COMMENT 'Date time',
  `status` int(11) NOT NULL DEFAULT '0' COMMENT 'Status \n0 - not sended\n1 - sended.',
  PRIMARY KEY (`message_id`),
  KEY `receiver_id_user_id` (`receiver_id`),
  KEY `sender_id_user_id` (`sender_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

delimiter $$

CREATE TABLE `msg_user` (
  `message_id` char(32) NOT NULL COMMENT 'Id of message',
  `user_id` char(32) NOT NULL COMMENT 'Id of user',
  `has_read` int(11) DEFAULT '0' COMMENT 'Status \n1 - read\n0 - unread',
  PRIMARY KEY (`message_id`,`user_id`),
  KEY `user_id_user_id` (`user_id`),
  KEY `message_id_message_id` (`message_id`),
  CONSTRAINT `msg_user_message_id` FOREIGN KEY (`message_id`) REFERENCES `msg_messages` (`message_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

delimiter $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `msg_mark_message_has_read`(
message_id char(32),
receiver_id varchar(32)
)
BEGIN

declare flag bit;

select exists(select mu.message_id from msg_user mu 
where mu.message_id = message_id
  and mu.`user_id` = receiver_id) into flag;

if flag = 0 then
  insert into 
    msg_user (message_id, `user_id`, has_read) 
    values(message_id, receiver_id, 1);
else
  update msg_user mu
    set has_read = 1
    where mu.message_id = message_id
    and mu.`user_id` = receiver_id;
end if;

END$$
